package com.dai.chun.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.dai.chun.dao.dao.SubsidyDao;
import com.dai.chun.dao.model.Employee;
import com.dai.chun.dao.model.Subsidy;
import com.dai.chun.dbc.DBManager;
import com.dai.chun.util.PageSplitUtil;

public class SubsidyDaoImpl implements SubsidyDao {
	private DBManager manager = DBManager.getInstance();

	public List<Subsidy> listSubsidy(PageSplitUtil psu) {
		String sql = "select * from t_subsidy limit ?,?";
		List<Subsidy> subsidys = new ArrayList<Subsidy>();
		Connection conn = null;
		try {
			conn = manager.getConnection();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		PreparedStatement preparedStatement = manager.createPRPstmt(conn, sql);
		try {
			preparedStatement.setInt(1, psu.getStart());
			preparedStatement.setInt(2, psu.getEnd());
			ResultSet res = preparedStatement.executeQuery();

			while (res.next()) {
				Subsidy subsidy = new Subsidy();
				subsidy.setSubsidy_id(res.getInt(1));
				subsidy.setSubsidy_content(res.getString(2));
				subsidy.setSubsidy_dept_id(res.getInt(3));
				subsidy.setSubsidy_job_id(res.getInt(4));
				subsidy.setSubsidy_amount(res.getFloat(5));
				subsidy.setSubsity_status(res.getInt(6));
				subsidys.add(subsidy);
			}
		} catch (Exception e) {
		} finally {
			manager.close(preparedStatement);
		}
		return subsidys;
	}

	public Boolean save(Subsidy subsidy) throws SQLException {
		boolean flag = false;
		String sql = "insert into t_subsidy (subsidy_content,subsidy_dept_id,subsidy_job_id,subsidy_amount,subsity_status) value (?,?,?,?,?)";
		Connection conn = manager.getConnection();
		PreparedStatement preparedStatement = manager.createPRPstmt(conn, sql);
		try {
			
			preparedStatement.setString( 1,subsidy.getSubsidy_content());
			preparedStatement.setInt( 2,subsidy.getSubsidy_dept_id());
			preparedStatement.setInt(3, subsidy.getSubsidy_job_id());
			preparedStatement.setFloat(4, subsidy.getSubsidy_amount());
			preparedStatement.setInt(5, subsidy.getSubsity_status());
			int count=preparedStatement.executeUpdate();
			if (count > 0) {
				flag = true;
			}
		} catch (Exception e) {
		} finally {
			manager.close(preparedStatement);
		}
		return flag;
	}

	@Override
	public int findAllCount() throws SQLException {
		int ret = 0;
		String sql = "select count(*) from t_subsidy";
		Connection conn = manager.getConnection();
		java.sql.Statement sm = conn.createStatement();
		ResultSet rs = sm.executeQuery(sql);
		if (rs.next()) {
			ret = rs.getInt(1);
		}
		manager.close(sm);
		return ret;
	}

	@Override
	public int delSubsidy(int subsidy_id) throws SQLException {
		int ret = 0;
		String sql = "delete from t_subsidy where subsidy_id=?";
		Connection conn = manager.getConnection();
		PreparedStatement preparedStatement = manager.createPRPstmt(conn, sql);
		preparedStatement.setInt(1, subsidy_id);
		ret = preparedStatement.executeUpdate();
		manager.close(preparedStatement);
		return ret;

	}

	@Override
	public int updateSubsidy(Subsidy subsidy) throws SQLException {
		int ret = 0;
		String sql = "update t_subsidy set subsidy_content=?,subsidy_dept_id=?," +
				"subsidy_job_id=?,subsidy_amount=?,subsity_status=? where subsidy_id=?";
		Connection conn = manager.getConnection();
		PreparedStatement preparedStatement = manager.createPRPstmt(conn, sql);
		try {
			preparedStatement.setString( 1,subsidy.getSubsidy_content());
			preparedStatement.setInt( 2,subsidy.getSubsidy_dept_id());
			preparedStatement.setInt(3, subsidy.getSubsidy_job_id());
			preparedStatement.setFloat(4, subsidy.getSubsidy_amount());
			preparedStatement.setInt(5, subsidy.getSubsity_status());
			preparedStatement.setInt(6, subsidy.getSubsidy_id());
			int count = preparedStatement.executeUpdate(); //
			if (count > 0) {
				ret = 1;
			}
		} catch (Exception e) {
		} finally {
			manager.close(preparedStatement);
		}
		return ret;
	}

	@Override
	public Subsidy findSubsidyById(int subsidy_id) {
		String sql = "select * from t_subsidy where subsidy_id=?";
		Subsidy subsidy = new Subsidy();
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		try {
			conn = manager.getConnection();
			preparedStatement = manager.createPRPstmt(conn, sql);
			preparedStatement.setInt(1, subsidy_id);
			ResultSet res = preparedStatement.executeQuery();
			while (res.next()) {
				subsidy.setSubsidy_id(res.getInt(1));
				subsidy.setSubsidy_content(res.getString(2));
				subsidy.setSubsidy_dept_id(res.getInt(3));
				subsidy.setSubsidy_job_id(res.getInt(4));
				subsidy.setSubsidy_amount(res.getFloat(5));
				subsidy.setSubsity_status(res.getInt(6));
			}
		} catch (Exception e) {
		} finally {
			manager.close(preparedStatement);
		}
		return subsidy;
	}

	@Override
	public List<Subsidy> listSubsidyByDeptId(PageSplitUtil psu, int dept_id) {
		String sql = "select * from t_subsidy  where subsidy_dept_id=? limit ?,?";
		List<Subsidy> subsidys = new ArrayList<Subsidy>();
		Connection conn = null;
		try {
			conn = manager.getConnection();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		PreparedStatement preparedStatement = manager.createPRPstmt(conn, sql);
		try {
			preparedStatement.setInt(1, dept_id);
			preparedStatement.setInt(2, psu.getStart());
			preparedStatement.setInt(3, psu.getEnd());
			ResultSet res = preparedStatement.executeQuery();

			while (res.next()) {
				Subsidy subsidy = new Subsidy();
				subsidy.setSubsidy_id(res.getInt(1));
				// subsidy.setSubsidy_name(res.getString(2));
				subsidys.add(subsidy);
			}
		} catch (Exception e) {
		} finally {
			manager.close(preparedStatement);
		}
		return subsidys;
	}

	@Override
	public List<Subsidy> getCurSubsidyList(Employee employee) {
		String sql = "select * from t_subsidy  where subsidy_dept_id=? and subsidy_job_id=?";
		List<Subsidy> subsidys = new ArrayList<Subsidy>();
		Connection conn = null;
		try {
			conn = manager.getConnection();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		PreparedStatement preparedStatement = manager.createPRPstmt(conn, sql);
		try {
			preparedStatement.setInt(1, employee.getEmp_dept_id());
			preparedStatement.setInt(2, employee.getEmp_job_id());
			ResultSet res = preparedStatement.executeQuery();

			while (res.next()) {
				Subsidy subsidy = new Subsidy();
				subsidy.setSubsidy_id(res.getInt(1));
				subsidy.setSubsidy_content(res.getString(2));
				subsidy.setSubsidy_dept_id(res.getInt(3));
				subsidy.setSubsidy_job_id(res.getInt(4));
				subsidy.setSubsidy_amount(res.getFloat(5));
				subsidy.setSubsity_status(res.getInt(6));
				subsidys.add(subsidy);
			}
		} catch (Exception e) {
		} finally {
			manager.close(preparedStatement);
		}
		return subsidys;
	}

	@Override
	public List<Subsidy> getCurSubsidyList(int[] effect_subsidys) {
		String sql = "select * from t_subsidy where subsidy_id=?";
		List<Subsidy> subsidys = new ArrayList<Subsidy>();
		Connection conn = null;
		try {
			conn = manager.getConnection();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		PreparedStatement preparedStatement = manager.createPRPstmt(conn, sql);
		try {
			for(Integer wage_id:effect_subsidys){
				if(0!=wage_id){
					preparedStatement.setInt(1,wage_id);
					ResultSet res = preparedStatement.executeQuery();
					while (res.next()) {
						Subsidy subsidy = new Subsidy();
						subsidy.setSubsidy_id(res.getInt(1));
						subsidy.setSubsidy_content(res.getString(2));
						subsidy.setSubsidy_dept_id(res.getInt(3));
						subsidy.setSubsidy_job_id(res.getInt(4));
						subsidy.setSubsidy_amount(res.getFloat(5));
						subsidy.setSubsity_status(res.getInt(6));
						subsidys.add(subsidy);
				}
			}
			

			
			}
		} catch (Exception e) {
		} finally {
			manager.close(preparedStatement);
		}
		return subsidys;
	}

}
